﻿using BLL;
using System;
using System.Web.UI.WebControls;

namespace Thksoft.Module.StatisticalReport
{
    public partial class YearDataReport : BaseUI.BasePage
    {
        private QX_PLANYEARAPPLYBLL planYearApplayBLL = new QX_PLANYEARAPPLYBLL();
        private QS_PROJECTBLL projectBLL = new QS_PROJECTBLL();
        private QX_PLANYEAR_DETAILBLL planYearDetailBLL = new QX_PLANYEAR_DETAILBLL();
        private QS_PLANMONTHAPPLYBLL planMonthApplyBLL = new QS_PLANMONTHAPPLYBLL();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                var key = Request.QueryString["ID"];
                if (key == "")
                {
                    MessageBox.Show(this, "参数ID传递错误");
                    return;
                }
                Key = Convert.ToDecimal(key);
                BindData();
            }
        }

        public decimal Key
        {
            get
            {
                var text = FORM_KEY.Value;
                if (text == "")
                {
                    return -1;
                }
                return Convert.ToDecimal(text);
            }
            set
            {
                FORM_KEY.Value = value.ToString();
            }
        }

        protected void SORTING_EVENT(object sender, GridViewSortEventArgs e)
        {
            string sPage = e.SortExpression;
            string SortOrder, OrderDire;
            SortOrder = ViewState["ORDER"] == null ? null : (string)ViewState["ORDER"];
            OrderDire = ViewState["ORDER_TYPE"] == null ? null : (string)ViewState["ORDER_TYPE"];
            if (SortOrder == sPage)
            {
                if (OrderDire == "DESC")
                    OrderDire = "ASC";
                else
                    OrderDire = "DESC";
            }
            else
            {
                SortOrder = e.SortExpression;
                OrderDire = "ASC";
            }
            ViewState["ORDER"] = SortOrder;
            ViewState["ORDER_TYPE"] = OrderDire;
            BindData();
        }

        protected void BindData()
        {
            var entity = planYearApplayBLL.Find(Key);

            //查询年 所有交易记录
            var sql = @"select to_char(a.n_month) n_month,
       into_amount,
       nvl(into_use,0) into_use,
       decode(into_amount,
              0,
              0,
              round(nvl(into_use, 0) / nvl(into_amount, 0) * 100, 2)) into_lv,
       out_amount,
      nvl( out_use,0)out_use ,

       decode(out_amount,
              0,
              0,
              round(nvl(out_use, 0) / nvl(out_amount, 0) * 100, 2)) out_lv,
       decode(amount_st,null,0,amount_st) amount_st,
       decode(amount_sum,null,0,amount_sum) amount_sum
  from (select t.n_month,
               sum(decode(d.n_type2, 1, d.n_amount, 0)) into_amount,
               sum(decode(d.n_type2, 2, d.n_amount, 0)) out_amount
          from qs_planmonthapply t, qs_planmonth_detail d
         where t.n_business_state = 2
           and t.n_company_id = {0}
           and t.n_year = {1}
           and t.n_monthapp_id = d.n_monthapp_id
           and d.n_type = 0
         group by t.n_month) a
  left join (select w.n_month,
                    sum(decode(d.n_type2, 1, d.N_USEAMOUNT, 0)) into_use,
                    sum(decode(d.n_type2, 2, d.N_USEAMOUNT, 0)) out_use
               from qs_planweekapply w, qs_planweek_detail d
              where w.n_year = {1}
                and w.n_business_state = 2
                and w.n_company_id = {0}
                and w.n_weekapp_id = d.n_weekapp_id
                and d.n_type = 0
              group by w.n_month) b
    on (a.n_month = b.n_month)
  left join (select sum(decode(u.n_business_state, 2, u.n_amount)) amount_st,
                    sum(u.n_amount) amount_sum,
                    u.d_data
               from QS_URGENTFUNDSAPPLY u
              where u.n_company_id = {0}
                and to_char(u.d_data, 'yyyy') = '{1}'
              group by u.d_data) c
    on (a.n_month = to_char(c.d_data, 'mm'))
 order by a.n_month ";
            sql = string.Format(sql, entity.N_COMPANY_ID, entity.N_YEAR);
            var dt = planYearDetailBLL.Query(sql);

            if (dt != null && dt.Rows.Count > 0)
            {
                object[] o = new object[dt.Columns.Count];

                o[0] = "年合计";

                o[1] = dt.Compute("Sum(into_amount)", "");
                o[2] = dt.Compute("Sum(into_use)", "");

                if (o[1].ToString() == "0")
                {
                    o[3] = 0;
                }
                else
                {
                    o[3] = Math.Round(decimal.Parse(o[2].ToString()) / decimal.Parse(o[1].ToString()) * 100, 2);
                }

                o[4] = dt.Compute("Sum(out_amount)", "");
                o[5] = dt.Compute("Sum(out_use)", "");

                if (o[4].ToString() == "0")
                {
                    o[6] = 0;
                }
                else
                {
                    o[6] = Math.Round(decimal.Parse(o[5].ToString()) / decimal.Parse(o[4].ToString()) * 100, 2);
                }

                o[7] = dt.Compute("Sum(amount_st)", "");
                o[8] = dt.Compute("Sum(amount_sum)", "");

                dt.Rows.Add(o);
            }

            /*string slq2 = @" select sum(decode(t.n_type2, 1, t.n_amount)) into_amount,
       sum(decode(t.n_type2, 2, t.n_amount)) out_amount
  from qx_planyear_detail t
 where t.n_yearapp_id = {0}
   and t.n_type = 0 ";*/
            string slq2 = @"select into_amount, out_amount,
            decode(amount_st,null,0,amount_st) amount_st,
            decode(amount_sum,null,0,amount_sum) amount_sum
  from (select sum(decode(t.n_type2, 1, t.n_amount)) into_amount,
               sum(decode(t.n_type2, 2, t.n_amount)) out_amount,
               t1.N_YEAR
          from qx_planyear_detail t, qx_planyearapply t1
         where t.n_yearapp_id = {0}
           and t.n_type = 0
           and t.n_yearapp_id = t1.n_yearapp_id
         group by t1.N_YEAR) a
  left join (select sum(decode(u.n_business_state, 2, u.n_amount)) amount_st,
                    sum(u.n_amount) amount_sum,
                    u.d_data
               from QS_URGENTFUNDSAPPLY u
              where u.n_company_id = {2}
                and to_char(u.d_data, 'yyyy') = '{1}'
              group by u.d_data) c
    on (a.N_YEAR = to_char(c.d_data, 'yyyy'))";

            slq2 = string.Format(slq2, entity.N_YEARAPP_ID, entity.N_YEAR, entity.N_COMPANY_ID);
            var dt2 = planYearDetailBLL.Query(slq2);

            if (dt2 != null && dt2.Rows.Count > 0)
            {
                object[] o = new object[dt.Columns.Count];

                o[0] = "年计划";

                o[1] = dt2.Rows[0][0];

                o[4] = dt2.Rows[0][1];

                o[7] = dt2.Rows[0][2];

                o[8] = dt2.Rows[0][3];
                dt.Rows.Add(o);
            }

            TABLE.DataSource = dt;
            TABLE.DataBind();

            Label1.Text = entity.N_YEAR.ToString() + "年资金计划";
        }

        protected void btnDaoChu_Click(object sender, EventArgs e)
        {
            toExecl1(Panel1, Label1.Text + DateTime.Now.ToShortDateString());
        }
    }
}